using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using Customs.Components;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
namespace Customs.Components.KDT.GC
{
    public class NhomSanPham : NhomSanPhamInfo
    {
        public bool load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this.DVGC = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) this.NgaykyHopDong = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNhomSP"))) this.MaNhomSP = reader.GetString(reader.GetOrdinal("MaNhomSP"));
                if (!reader.IsDBNull(reader.GetOrdinal("GiaGiaCong"))) this.GiaGiaCong = reader.GetDecimal(reader.GetOrdinal("GiaGiaCong"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                reader.Close();
                return true;
            }
            reader.Close();
            return false;
        }
        public void Load(NhomSanPhamInfo nhomsp)
        {
            this.SoHopDong = nhomsp.SoHopDong;
            this.SoLuong = nhomsp.SoLuong;
            this.STTHang = nhomsp.STTHang;
            this.NgaykyHopDong = nhomsp.NgaykyHopDong;
            this.MaNhomSP = nhomsp.MaNhomSP;
            this.MaHaiQuan = nhomsp.MaHaiQuan;            
            this.GiaGiaCong = nhomsp.GiaGiaCong;
            this.DVGC = nhomsp.DVGC;
            this.MaDuKien = nhomsp.MaDuKien;
        }
        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("Ma_SPGC AS [MaNhomSP], ");
            query.Append("Ma_DuKien AS [MaDuKien], ");
            query.Append("So_Luong AS [SoLuong], ");
            query.Append("Gia_GC AS [GiaGiaCong], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDLOAISPGC] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy and Ma_SPGC=@MaNhomSP");

            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this._MaHQHD;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = this._DVGC;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = this._ngayKy;

            parameters[4] = new SqlParameter("@MaNhomSP", SqlDbType.VarChar);
            parameters[4].Value = this._maNhomSP;
            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }

        //---------------------------------------------------------------------------------------------
        public int Delete(SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDLOAISPGC WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and Ma_SPGC='{4}'",this.SoHopDong,this.MaHaiQuan,this.DVGC,this.NgaykyHopDong,this.MaNhomSP);
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
        }
        public int Delete()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string query = string.Format("DELETE FROM tblDLOAISPGC WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and Ma_SPGC='{4}'", this.SoHopDong, this.MaHaiQuan, this.DVGC, this.NgaykyHopDong, this.MaNhomSP);
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, query);
        }
        //---------------------------------------------------------------------------------------------
        public void Delete(SqlTransaction transaction, NhomSanPhamInfoCollection collection)
        {
            NhomSanPham nhomsp = new NhomSanPham();
            foreach (NhomSanPhamInfo itemInfo in collection)
            {
                nhomsp.Load(itemInfo);
                nhomsp.Delete();
            }
        }
        //---------------------------------------------------------------------------------------------
        public bool Delete(NhomSanPhamInfoCollection collection)
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Delete(transaction, collection);
                    transaction.Commit();
                    returnValue = true;
                }
                catch
                {
                    returnValue = false;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction, NhomSanPhamInfoCollection collection)
        {
            NhomSanPham nhomsp = new NhomSanPham();
            foreach (NhomSanPhamInfo itemInfo in collection)
            {
                nhomsp.Load(itemInfo);
                nhomsp.InsertTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDLOAISPGC ( ");
            query.Append("[So_HD], ");
            query.Append("[Ma_HQHD], ");
            query.Append("[DVGC], ");
            query.Append("[Ngay_Ky], ");
            query.Append("[Ma_SPGC], ");
            query.Append("[Ma_DuKien], ");
            query.Append("[So_Luong], ");
            query.Append("[Gia_GC], ");
            query.Append("[STTHang] ) ");

            query.Append("VALUES ( ");

            query.Append("@So_HD, ");
            query.Append("@Ma_HQHD, ");
            query.Append("@DVGC, ");
            query.Append("@Ngay_Ky, ");
            query.Append("@Ma_SPGC, ");
            query.Append("@Ma_DuKien, ");
            query.Append("@So_Luong, ");
            query.Append("@Gia_GC, ");
            query.Append("@STTHang )");

            SqlParameter[] parameters = new SqlParameter[9];

            parameters[0] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[0].Value = this.SoHopDong;

            parameters[1] = new SqlParameter("@Ma_HQHD", SqlDbType.VarChar, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[2].Value = this.DVGC;

            parameters[3] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[3].Value = this.NgaykyHopDong;

            parameters[4] = new SqlParameter("@Ma_SPGC", SqlDbType.VarChar, 14);
            parameters[4].Value = this.MaNhomSP;

            parameters[5] = new SqlParameter("@Ma_DuKien", SqlDbType.NVarChar);
            parameters[5].Value = this.MaDuKien;

            parameters[6] = new SqlParameter("@So_Luong", SqlDbType.Decimal);
            parameters[6].Value = this.SoLuong;

            parameters[7] = new SqlParameter("@Gia_GC", SqlDbType.Money);
            parameters[7].Value = this.GiaGiaCong;

            parameters[8] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[8].Value = this.STTHang;

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }

        public static NhomSanPhamInfoCollection selectNhomSanPhamInfoCollectionByHopDong(string mahaiquan, string madoanhnghiep, string sohopdong, DateTime ngayky)
        {
            NhomSanPhamInfoCollection nhomspCollection = new NhomSanPhamInfoCollection();
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("Ma_SPGC AS [MaNhomSP], ");
            query.Append("Ma_DuKien AS [MaDuKien], ");
            query.Append("So_Luong AS [SoLuong], ");
            query.Append("Gia_GC AS [GiaGiaCong], ");
            query.Append("STTHang AS [STTHang] ");
            query.Append("FROM dbo.[tblDLOAISPGC] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy ");

            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = sohopdong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[1].Value = mahaiquan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = madoanhnghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = ngayky;

            IDataReader reader= SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
            while (reader.Read())
            {
                NhomSanPhamInfo nhomInfo = new NhomSanPhamInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) nhomInfo.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) nhomInfo.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) nhomInfo.DVGC = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) nhomInfo.NgaykyHopDong = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNhomSP"))) nhomInfo.MaNhomSP = reader.GetString(reader.GetOrdinal("MaNhomSP"));
                if (!reader.IsDBNull(reader.GetOrdinal("GiaGiaCong"))) nhomInfo.GiaGiaCong = reader.GetDecimal(reader.GetOrdinal("GiaGiaCong"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) nhomInfo.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) nhomInfo.SoLuong = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                nhomspCollection.Add(nhomInfo);
            }
            reader.Close();
            return nhomspCollection;
        }
    }
}
